package lmteam.userservice;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import lmteam.common.*;

import org.apache.commons.logging.Log;
import org.apache.axis.components.logger.LogFactory;

import java.io.*;
import java.util.Vector;

public class UserServiceProcessor
    implements IUserAccess
{
    private Connection connection;
    private Statement  statement;

    private final String databaseloc = "jdbc:sqlite:lmteamdatabase.db";

    private static Log log = LogFactory.getLog("IUserAccessSoapBindingImpl");

    public UserServiceProcessor()
    {
	log.error("Hello WOrldQ@@@");
    }

    public lmteam.common.User get(java.lang.String username) throws java.rmi.RemoteException
    {
	String queryString;
	ResultSet resultSet;
	Role roles;
	User retUser;
	int errorflag;

	queryString = "SELECT * FROM users WHERE username=\'" + username +"\';";
	resultSet = null;
	retUser = null;
	try
	    {
		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection(databaseloc);
		statement = connection.createStatement();
		resultSet = statement.executeQuery(queryString);
		if ( resultSet.isClosed() != true )
		    {
		
			resultSet.next();
						
			System.out.println("resultSet is <" + resultSet.getString("username") + ">" );
			System.out.println("resultSet is <" + resultSet.getString("password") + ">" );
			System.out.println("resultSet is <" + resultSet.getString("role_approver") + ">" );
			System.out.println("resultSet is <" + resultSet.getString("role_reviewer") + ">" );
			System.out.println("resultSet is <" + resultSet.getString("role_submitter") + ">" );
			
			roles = new Role( (resultSet.getString("role_approver").compareTo("1") == 0),
					  (resultSet.getString("role_reviewer").compareTo("1") == 0),
					  (resultSet.getString("role_submitter").compareTo("1") == 0) );
			retUser = new User( resultSet.getString("username"),
					    resultSet.getString("password"),
					    roles );
		    }

	    }
	catch (Exception e)
	    {
		e.printStackTrace();		
	    }
	finally
	    {
		try
		    {
			System.out.println("Closing");
			if ( resultSet != null )
			    if ( resultSet.isClosed() == false )
				resultSet.close();
			statement.close();
			connection.close();
		    }
		catch (Exception e )
		    {
			e.printStackTrace();
		    }
	    }


	return retUser;
    }

    public boolean delete(java.lang.String username) throws java.rmi.RemoteException
    {
	String queryString;
	int ret;

	queryString = "DELETE FROM users WHERE username=\'" + username + "\';";
	try
	    {
      		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection(databaseloc);
		statement = connection.createStatement();
	        ret = statement.executeUpdate(queryString);
	
		System.out.println("ret (Delete) is " + ret );
		ret = 0; // reset this to all good
	    }
	catch (Exception e)
	    {
		e.printStackTrace();
		ret = -1;
	    }
	finally
	    {
		try
		    {
			System.out.println("Closing - 3");
			statement.close();
			connection.close();
		    }
		catch (Exception e)
		    {
			e.printStackTrace();
		    }
	    }

	return (ret == 0);
    }

    public boolean update(lmteam.common.User userdata, java.lang.String password) throws java.rmi.RemoteException
    {
	String queryString;
	String username;
	int ret;

	// changing the password
	username = userdata.getUsername();
	queryString = "UPDATE users SET password=\'" + password + "\' WHERE username=\'" + username + "\';";
	ret = 1200;
	try
	    {
      		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection(databaseloc);
		statement = connection.createStatement();
	        ret = statement.executeUpdate(queryString);
	
		System.out.println("ret3 is " + ret );
		ret = 0; // reset this to all good
	    }
	catch (Exception e)
	    {
		System.out.println("error???2");
		e.printStackTrace();
		ret = -1;
	    }
	finally
	    {
		try
		    {
			System.out.println("Closing - 4 ");
			statement.close();
			connection.close();
		    }
		catch (Exception e)
		    {
			e.printStackTrace();
		    }
	    }

	if ( ret == 0 )
	    return true;

	return false;
    }

    public boolean update(lmteam.common.User userdata, lmteam.common.Role roles, boolean add) throws java.rmi.RemoteException
    {
	String queryString;
	String username;
	int ra, rs, rr;
	int ret;

	// changing the password
	username = userdata.getUsername();
	ra = getBool( roles.isApprover() );
	rs = getBool( roles.isSubmitter() );
	rr = getBool( roles.isReviewer() );
	queryString = "UPDATE users SET role_approver=" + ra + ", role_reviewer=" + rr +
	    ", role_submitter=" + rr + " WHERE username=\'" + username + "\';";
	ret = 1200;
	try
	    {
      		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection(databaseloc);
		statement = connection.createStatement();
	        ret = statement.executeUpdate(queryString);
	
		System.out.println("ret3 is " + ret );
		ret = 0; // reset this to all good
	    }
	catch (Exception e)
	    {
		System.out.println("error???2");
		e.printStackTrace();
		ret = -1;
	    }
	finally
	    {
		try
		    {
			System.out.println("Closing - 4 ");
			statement.close();
			connection.close();
		    }
		catch (Exception e)
		    {
			e.printStackTrace();
		    }
	    }

	if ( ret == 0 )
	    return true;

	return false;
    }

    public boolean create(java.lang.String username, java.lang.String password) throws java.rmi.RemoteException
    {
	String queryString;
	int ret;

	// set roles to nothing by default
	queryString = "INSERT INTO users (username,password,role_approver,role_reviewer,role_submitter) values (\'" + 
	    username + "\',\'" + password + "\',0,0,0);";
	ret = 1200;
	try
	    {
      		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection(databaseloc);
		statement = connection.createStatement();
	        ret = statement.executeUpdate(queryString);
	
		//System.out.println("ret is " + ret );
		ret = 0; // reset this to all good
	    }
	catch (Exception e)
	    {
		//System.out.println("error???");
		e.printStackTrace();
		ret = -1;
	    }
	finally
	    {
		try
		    {
			//System.out.println("Closing - 2 ");
			statement.close();
			connection.close();
		    }
		catch (Exception e)
		    {
			e.printStackTrace();
		    }
	    }

	if ( ret == 0 )
	    return true;

	return false;
    } 

    public lmteam.common.User[] getUsers() throws java.rmi.RemoteException
    {
	String queryString;
	ResultSet resultSet;
	Role roles;
	User retUser;
	int errorflag;
	Vector<User> outusers;

	queryString = "SELECT * FROM users;";
	resultSet = null;
	retUser = null;
	outusers = new Vector<User>();
	try
	    {
		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection(databaseloc);
		statement = connection.createStatement();
		resultSet = statement.executeQuery(queryString);
		if ( resultSet.isClosed() != true )
		    {	       
			while ( resultSet.next() )
			    {										
				roles = new Role( (resultSet.getString("role_approver").compareTo("1") == 0),
						  (resultSet.getString("role_reviewer").compareTo("1") == 0),
						  (resultSet.getString("role_submitter").compareTo("1") == 0) );
				retUser = new User( resultSet.getString("username"),
						    resultSet.getString("password"),
						    roles );
				
				outusers.add( retUser );
			    }
		    }

	    }
	catch (Exception e)
	    {
		e.printStackTrace();		
	    }
	finally
	    {
		try
		    {
			System.out.println("Closing");
			if ( resultSet != null )
			    if ( resultSet.isClosed() == false )
				resultSet.close();
			statement.close();
			connection.close();
		    }
		catch (Exception e )
		    {
			e.printStackTrace();
		    }
	    }

	User[] out;
	out = new User[ outusers.size() ];
	out = outusers.toArray( out );

	System.out.println("users is " + out );
	return out;
    }

    private int getBool( boolean bool )
    {
	if ( bool )
	    return 1;
	
	return 0;
    }
}